﻿Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Microsoft.Reporting.WebForms
Imports log4net

Partial Class Report_RP05_2
    Inherits System.Web.UI.Page

    Dim Report As Object
    Public ScriptText As String
    Private Shared logger As ILog = LogManager.GetLogger("Report_RP05_2")

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Session("users") Is Nothing Then
            ScriptText = "<script> wclose();</script>"
        End If
    End Sub
    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        'If Session("fundcenterS") = "" Then
        '    Session("fundcenterS") = "0011"
        '    Session("fundcenterE") = "0012"

        'End If

        'If Session("users") = "" Then
        '    Session("users") = "wassana"
        'End If
        'Session("year") = 2556
        'Session("version") = 2

        If Session("users") Is Nothing Then
            Response.Redirect("../LoginForm.aspx")
        End If


        Getdata()


    End Sub


    Function Getdata() As Decimal

        Dim sumadap As New Data_ReportTableAdapters.SumAmountTableAdapter
        Dim sumsql As String = ""
        sumsql += " SELECT ISNULL(SUM(BudgetDetails.Amount),0) AS Amount "
        sumsql += " from BudgetDetails, Cmmts, Products, FctParent "
        sumsql += " where BudgetDetails.Cmmt_CmmtCode = Cmmts.CmmtCode"
        sumsql += " and BudgetDetails.Product_ProductCode  =  Products.ProductCode"
        sumsql += " and FctParent.FundCenterCode =BudgetDetails.FundCenter_FundCenterCode"



        sumsql += " and  BudgetDetails.PlanYear  = " & Session("year") & " and Version = '" & Session("version") & "'"

        sumsql += " and Cmmts.PlanYear = " & Session("year")
        sumsql += " and Products.PlanYear = " & Session("year")
        sumsql += " and FctParent.planyear = " & Session("year")



        If Session("FCTRmode") = "1" Then
            sumsql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sumsql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        End If


        If Session("CmmtMode") = "1" Then
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sumsql += " and  Cmmts.CmmtCode  between '" & Session("CmmtCodeS") & "' and '" & Session("CmmtCodeE") & "'"
            End If
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) = 0 Then
                sumsql += " and  Cmmts.CmmtCode = '" & Session("CmmtCodeS") & "'"
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sumsql += " and  Cmmts.CmmtCode = '" & Session("CmmtCodeE") & "'"
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) = 0 Then
                sumsql += " and  Cmmts.CmmtCode  between " & "'00000000'" & " and " & "'99999999'"
            End If
        ElseIf Session("CmmtMode") = "2" Then
            If Session("CmmtParent") <> "" And Session("CmmtParent") <> "0" Then
                sumsql += "and cmmtcode in (select cmmt_cmmtcode from  cmmthierarchies where parentcmmthierarchy_id in (select id from cmmthierarchies "
                sumsql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sumsql += " or id in (select id from cmmthierarchies "
                sumsql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sumsql += ")"
            End If
        End If

        If Val(Session("ProductS")) <> 0 And Val(Session("ProductE")) <> 0 Then
            sumsql += "  and  Products.ProductCode between " & Session("ProductS") & " and " & Session("ProductE")
        End If

        Dim sumamount As Decimal
        sumamount = sumadap.GetAmountData(sumsql)


        Dim adapter As New Data_ReportTableAdapters.R05TableAdapter
        Dim sql As String
        Dim dt As New Data_Report.R05DataTable


        sql = "  select CASE WHEN parent1  NOT IN (" & Session("FundcenterAll") & ") THEN NULL ELSE parent1 END AS parent1 "
        sql += " ,CASE WHEN parent1  NOT IN (" & Session("FundcenterAll") & ") THEN NULL ELSE parent1_name END AS parent1_name "
        sql += " ,CASE WHEN parent2  NOT IN (" & Session("FundcenterAll") & ") THEN NULL ELSE parent2 END AS parent2 "
        sql += " ,CASE WHEN parent2  NOT IN (" & Session("FundcenterAll") & ") THEN NULL ELSE parent2_name END AS parent2_name"
        sql += " ,CASE WHEN parent3  NOT IN (" & Session("FundcenterAll") & ")THEN NULL ELSE parent3 END AS parent3 "
        sql += " ,CASE WHEN parent3  NOT IN (" & Session("FundcenterAll") & ") THEN NULL ELSE parent3_name END AS parent3_name"
        sql += " ,CASE WHEN parent4  NOT IN (" & Session("FundcenterAll") & ") THEN NULL ELSE parent4 END AS parent4 "
        sql += " ,CASE WHEN parent4  NOT IN (" & Session("FundcenterAll") & ") THEN NULL ELSE parent4_name END AS parent4_name"
        sql += " ,CASE WHEN parent5  NOT IN (" & Session("FundcenterAll") & ") THEN NULL ELSE parent5 END AS parent5 "
        sql += " ,CASE WHEN parent5  NOT IN (" & Session("FundcenterAll") & ") THEN NULL ELSE parent5_name END AS parent5_name"
        sql += " ,Cmmts.CmmtCode,Cmmts.Description ,BudgetDetails.Product_ProductCode,Products.Description, SUM(BudgetDetails.Amount) AS Amount "
        If sumamount = 0 Then
            sql += " ,0  as percen"
        Else
            sql += " ,(SUM(BudgetDetails.Amount) / " & sumamount & ") * 100 as percen"
        End If
        sql += " from BudgetDetails, Cmmts, Products, FctParent "
        sql += " where BudgetDetails.Cmmt_CmmtCode = Cmmts.CmmtCode"
        sql += " and BudgetDetails.Product_ProductCode  =  Products.ProductCode"
        sql += " and FctParent.FundCenterCode =BudgetDetails.FundCenter_FundCenterCode"



        sql += " and  BudgetDetails.PlanYear  = " & Session("year") & " and Version = '" & Session("version") & "'"

        sql += " and Cmmts.PlanYear = " & Session("year")
        sql += " and Products.PlanYear = " & Session("year")
        sql += " and FctParent.planyear = " & Session("year")


        If Session("FCTRmode") = "1" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        End If


        If Session("CmmtMode") = "1" Then
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  Cmmts.CmmtCode  between '" & Session("CmmtCodeS") & "' and '" & Session("CmmtCodeE") & "'"
            End If
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  Cmmts.CmmtCode = '" & Session("CmmtCodeS") & "'"
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  Cmmts.CmmtCode = '" & Session("CmmtCodeE") & "'"
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  Cmmts.CmmtCode  between " & "'00000000'" & " and " & "'99999999'"
            End If
        ElseIf Session("CmmtMode") = "2" Then
            If Session("CmmtParent") <> "" And Session("CmmtParent") <> "0" Then
                sql += "and cmmtcode in (select cmmt_cmmtcode from  cmmthierarchies where parentcmmthierarchy_id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += " or id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += ")"
            End If
        End If

        If Val(Session("ProductS")) <> 0 And Val(Session("ProductE")) <> 0 Then
            sql += "  and  Products.ProductCode between " & Session("ProductS") & " and " & Session("ProductE")
        End If

        sql += " group by  parent1,parent1_name ,parent2 ,parent2_name,parent3 ,parent3_name,parent4 ,parent4_name,parent5 ,parent5_name,"
        sql += " Cmmts.CmmtCode, Cmmts.Description, BudgetDetails.Product_ProductCode, Products.Description "







        ' Response.Write(sql)
        'Exit Function
        adapter.SearchReport(dt, sql)



        'Return 1
        'Exit Function
        dt.TableName = "R05DT2"
        Dim das As New DataSet
        das.DataSetName = "R01DS"
        das.Tables.Add(dt)
        ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local
        'ReportViewer1.LocalReport.ReportPath = System.Environment.CurrentDirectory & "Report\R01.rdlc"
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", das.Tables("R05DT2")))
        ReportViewer1.DocumentMapCollapsed = True

        Dim UserName As String = Session("users")
        Dim years As String = Session("year")
        Dim version As String = Session("version")
        Dim users As String = Session("users")

        Dim p1, p2, p3 As ReportParameter
        p1 = New ReportParameter("year", years)
        p2 = New ReportParameter("version", version)
        p3 = New ReportParameter("username", UserName)

        ReportViewer1.LocalReport.SetParameters(p1)
        ReportViewer1.LocalReport.SetParameters(p2)
        ReportViewer1.LocalReport.SetParameters(p3)


        ReportViewer1.LocalReport.Refresh()


        'Response.Write(dt.Rows.Count & sql)
        Session("count") = dt.Rows.Count



        Return dt.Rows.Count

    End Function


    Public Function nPageNumber() As String
        Dim str As String
        str = Me.Report.Globals!PageNumber.ToString()
        Return str
    End Function

End Class
